--    Program: 		CAPPOIMP_PURCHASE_ORDER_LINE_LOCATION.sql
--    Description:  Import PO Line Locations All information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp    		varchar2(2000);
   
   l_line_location_id		number;
                    		                 		
   cursor cur_line_locations_all is
      select * from CRST_PO_LINE_LOCATIONS_ALL for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_line_locations_all loop   	
     l_err_msg_tmp := '';
     
     select PO_LINE_LOCATIONS_S.NEXTVAL 
     INTO l_line_location_id
     FROM DUAL;
     
     update CRST_PO_LINE_LOCATIONS_ALL
     set    line_location_id_n = l_line_location_id
     where  line_location_id = cur.LINE_LOCATION_ID;
     
     begin    
       -- import data
       insert into PO_LINE_LOCATIONS_ALL 
       (
            LINE_LOCATION_ID                ,
            LAST_UPDATE_DATE                ,
            LAST_UPDATED_BY                 ,
            PO_HEADER_ID                    ,
            PO_LINE_ID                      ,
            LAST_UPDATE_LOGIN               ,
            CREATION_DATE                   ,
            CREATED_BY                      ,
            QUANTITY                        ,
            QUANTITY_RECEIVED               ,
            QUANTITY_ACCEPTED               ,
            QUANTITY_REJECTED               ,
            QUANTITY_BILLED                 ,
            QUANTITY_CANCELLED              ,
            UNIT_MEAS_LOOKUP_CODE           ,
            PO_RELEASE_ID                   ,
            SHIP_TO_LOCATION_ID             ,
            SHIP_VIA_LOOKUP_CODE            ,
            NEED_BY_DATE                    ,
            PROMISED_DATE                   ,
            LAST_ACCEPT_DATE                ,
            PRICE_OVERRIDE                  ,
            ENCUMBERED_FLAG                 ,
            ENCUMBERED_DATE                 ,
            UNENCUMBERED_QUANTITY           ,
            FOB_LOOKUP_CODE                 ,
            FREIGHT_TERMS_LOOKUP_CODE       ,
            TAXABLE_FLAG                    ,
            TAX_NAME                        ,
            ESTIMATED_TAX_AMOUNT            ,
            FROM_HEADER_ID                  ,
            FROM_LINE_ID                    ,
            FROM_LINE_LOCATION_ID           ,
            START_DATE                      ,
            END_DATE                        ,
            LEAD_TIME                       ,
            LEAD_TIME_UNIT                  ,
            PRICE_DISCOUNT                  ,
            TERMS_ID                        ,
            APPROVED_FLAG                   ,
            APPROVED_DATE                   ,
            CLOSED_FLAG                     ,
            CANCEL_FLAG                     ,
            CANCELLED_BY                    ,
            CANCEL_DATE                     ,
            CANCEL_REASON                   ,
            FIRM_STATUS_LOOKUP_CODE         ,
            FIRM_DATE                       ,
            ATTRIBUTE_CATEGORY              ,
            ATTRIBUTE1                      ,
            ATTRIBUTE2                      ,
            ATTRIBUTE3                      ,
            ATTRIBUTE4                      ,
            ATTRIBUTE5                      ,
            ATTRIBUTE6                      ,
            ATTRIBUTE7                      ,
            ATTRIBUTE8                      ,
            ATTRIBUTE9                      ,
            ATTRIBUTE10                     ,
            UNIT_OF_MEASURE_CLASS           ,
            ENCUMBER_NOW                    ,
            ATTRIBUTE11                     ,
            ATTRIBUTE12                     ,
            ATTRIBUTE13                     ,
            ATTRIBUTE14                     ,
            ATTRIBUTE15                     ,
            INSPECTION_REQUIRED_FLAG        ,
            RECEIPT_REQUIRED_FLAG           ,
            QTY_RCV_TOLERANCE               ,
            QTY_RCV_EXCEPTION_CODE          ,
            ENFORCE_SHIP_TO_LOCATION_CODE   ,
            ALLOW_SUBSTITUTE_RECEIPTS_FLAG  ,
            DAYS_EARLY_RECEIPT_ALLOWED      ,
            DAYS_LATE_RECEIPT_ALLOWED       ,
            RECEIPT_DAYS_EXCEPTION_CODE     ,
            INVOICE_CLOSE_TOLERANCE         ,
            RECEIVE_CLOSE_TOLERANCE         ,
            SHIP_TO_ORGANIZATION_ID         ,
            SHIPMENT_NUM                    ,
            SOURCE_SHIPMENT_ID              ,
            SHIPMENT_TYPE                   ,
            CLOSED_CODE                     ,
            REQUEST_ID                      ,
            PROGRAM_APPLICATION_ID          ,
            PROGRAM_ID                      ,
            PROGRAM_UPDATE_DATE             ,
            USSGL_TRANSACTION_CODE          ,
            GOVERNMENT_CONTEXT              ,
            RECEIVING_ROUTING_ID            ,
            ACCRUE_ON_RECEIPT_FLAG          ,
            CLOSED_REASON                   ,
            CLOSED_DATE                     ,
            CLOSED_BY                       ,
            ORG_ID                          ,
            QUANTITY_SHIPPED                ,
            GLOBAL_ATTRIBUTE_CATEGORY       ,
            GLOBAL_ATTRIBUTE1               ,
            GLOBAL_ATTRIBUTE2               ,
            GLOBAL_ATTRIBUTE3               ,
            GLOBAL_ATTRIBUTE4               ,
            GLOBAL_ATTRIBUTE5               ,
            GLOBAL_ATTRIBUTE6               ,
            GLOBAL_ATTRIBUTE7               ,
            GLOBAL_ATTRIBUTE8               ,
            GLOBAL_ATTRIBUTE9               ,
            GLOBAL_ATTRIBUTE10              ,
            GLOBAL_ATTRIBUTE11              ,
            GLOBAL_ATTRIBUTE12              ,
            GLOBAL_ATTRIBUTE13              ,
            GLOBAL_ATTRIBUTE14              ,
            GLOBAL_ATTRIBUTE15              ,
            GLOBAL_ATTRIBUTE16              ,
            GLOBAL_ATTRIBUTE17              ,
            GLOBAL_ATTRIBUTE18              ,
            GLOBAL_ATTRIBUTE19              ,
            GLOBAL_ATTRIBUTE20              ,
            COUNTRY_OF_ORIGIN_CODE          ,
            TAX_USER_OVERRIDE_FLAG          ,
            MATCH_OPTION                    ,
            TAX_CODE_ID                     ,
            CALCULATE_TAX_FLAG              ,
            CHANGE_PROMISED_DATE_REASON     ,
            NOTE_TO_RECEIVER                ,
            SECONDARY_QUANTITY              ,
            SECONDARY_UNIT_OF_MEASURE       ,
            PREFERRED_GRADE                 ,
            SECONDARY_QUANTITY_RECEIVED     ,
            SECONDARY_QUANTITY_ACCEPTED     ,
            SECONDARY_QUANTITY_REJECTED     ,
            SECONDARY_QUANTITY_CANCELLED    ,
            VMI_FLAG                        ,
            CONSIGNED_FLAG                  ,
            RETROACTIVE_DATE                ,
            SUPPLIER_ORDER_LINE_NUMBER      
       )
       values
       (
            l_line_location_id                  ,
            cur.LAST_UPDATE_DATE                ,
            CUR.LAST_UPDATED_BY,
            CAPPOIMP_MAPPING.GetPOHeaderIDMapping(cur.PO_HEADER_ID),
            CAPPOIMP_MAPPING.GetPOLineIDMapping(cur.PO_LINE_ID),
            cur.LAST_UPDATE_LOGIN               ,
            cur.CREATION_DATE                   ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.QUANTITY                        ,
            cur.QUANTITY_RECEIVED               ,
            cur.QUANTITY_ACCEPTED               ,
            cur.QUANTITY_REJECTED               ,
            cur.QUANTITY_BILLED                 ,
            cur.QUANTITY_CANCELLED              ,
            cur.UNIT_MEAS_LOOKUP_CODE           ,
            cur.PO_RELEASE_ID                   ,
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.SHIP_TO_LOCATION_ID), 
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.SHIP_VIA_LOOKUP_CODE),
            cur.NEED_BY_DATE                    ,
            cur.PROMISED_DATE                   ,
            cur.LAST_ACCEPT_DATE                ,
            cur.PRICE_OVERRIDE                  ,
            cur.ENCUMBERED_FLAG                 ,
            cur.ENCUMBERED_DATE                 ,
            cur.UNENCUMBERED_QUANTITY           ,
            cur.FOB_LOOKUP_CODE                 ,
            cur.FREIGHT_TERMS_LOOKUP_CODE       ,
            cur.TAXABLE_FLAG                    ,
            cur.TAX_NAME                        ,
            cur.ESTIMATED_TAX_AMOUNT            ,
            CAPPOIMP_MAPPING.GetPOHeaderIDMapping(cur.FROM_HEADER_ID),
            CAPPOIMP_MAPPING.GetPOLineIDMapping(cur.FROM_LINE_ID),
            cur.FROM_LINE_LOCATION_ID           ,
            cur.START_DATE                      ,
            cur.END_DATE                        ,
            cur.LEAD_TIME                       ,
            cur.LEAD_TIME_UNIT                  ,
            cur.PRICE_DISCOUNT                  ,
            cur.TERMS_ID                        ,
            cur.APPROVED_FLAG                   ,
            cur.APPROVED_DATE                   ,
            cur.CLOSED_FLAG                     ,
            cur.CANCEL_FLAG                     ,
            cur.CANCELLED_BY                    ,
            cur.CANCEL_DATE                     ,
            cur.CANCEL_REASON                   ,
            cur.FIRM_STATUS_LOOKUP_CODE         ,
            cur.FIRM_DATE                       ,
            cur.ATTRIBUTE_CATEGORY              ,
            cur.ATTRIBUTE1                      ,
            cur.ATTRIBUTE2                      ,
            cur.ATTRIBUTE3                      ,
            cur.ATTRIBUTE4                      ,
            cur.ATTRIBUTE5                      ,
            cur.ATTRIBUTE6                      ,
            cur.ATTRIBUTE7                      ,
            cur.ATTRIBUTE8                      ,
            cur.ATTRIBUTE9                      ,
            cur.ATTRIBUTE10                     ,
            cur.UNIT_OF_MEASURE_CLASS           ,
            cur.ENCUMBER_NOW                    ,
            cur.ATTRIBUTE11                     ,
            cur.ATTRIBUTE12                     ,
            cur.ATTRIBUTE13                     ,
            cur.ATTRIBUTE14                     ,
            cur.ATTRIBUTE15                     ,
            cur.INSPECTION_REQUIRED_FLAG        ,
            cur.RECEIPT_REQUIRED_FLAG           ,
            cur.QTY_RCV_TOLERANCE               ,
            cur.QTY_RCV_EXCEPTION_CODE          ,
            cur.ENFORCE_SHIP_TO_LOCATION_CODE   ,
            cur.ALLOW_SUBSTITUTE_RECEIPTS_FLAG  ,
            cur.DAYS_EARLY_RECEIPT_ALLOWED      ,
            cur.DAYS_LATE_RECEIPT_ALLOWED       ,
            cur.RECEIPT_DAYS_EXCEPTION_CODE     ,
            cur.INVOICE_CLOSE_TOLERANCE         ,
            cur.RECEIVE_CLOSE_TOLERANCE         ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.SHIP_TO_ORGANIZATION_ID),
            cur.SHIPMENT_NUM                    ,
            cur.SOURCE_SHIPMENT_ID              ,
            cur.SHIPMENT_TYPE                   ,
            cur.CLOSED_CODE                     ,
            cur.REQUEST_ID                      ,
            cur.PROGRAM_APPLICATION_ID          ,
            cur.PROGRAM_ID                      ,
            cur.PROGRAM_UPDATE_DATE             ,
            cur.USSGL_TRANSACTION_CODE          ,
            cur.GOVERNMENT_CONTEXT              ,
            cur.RECEIVING_ROUTING_ID            ,
            cur.ACCRUE_ON_RECEIPT_FLAG          ,
            cur.CLOSED_REASON                   ,
            cur.CLOSED_DATE                     ,
            cur.CLOSED_BY                       ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.QUANTITY_SHIPPED                ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
            cur.GLOBAL_ATTRIBUTE1               ,
            cur.GLOBAL_ATTRIBUTE2               ,
            cur.GLOBAL_ATTRIBUTE3               ,
            cur.GLOBAL_ATTRIBUTE4               ,
            cur.GLOBAL_ATTRIBUTE5               ,
            cur.GLOBAL_ATTRIBUTE6               ,
            cur.GLOBAL_ATTRIBUTE7               ,
            cur.GLOBAL_ATTRIBUTE8               ,
            cur.GLOBAL_ATTRIBUTE9               ,
            cur.GLOBAL_ATTRIBUTE10              ,
            cur.GLOBAL_ATTRIBUTE11              ,
            cur.GLOBAL_ATTRIBUTE12              ,
            cur.GLOBAL_ATTRIBUTE13              ,
            cur.GLOBAL_ATTRIBUTE14              ,
            cur.GLOBAL_ATTRIBUTE15              ,
            cur.GLOBAL_ATTRIBUTE16              ,
            cur.GLOBAL_ATTRIBUTE17              ,
            cur.GLOBAL_ATTRIBUTE18              ,
            cur.GLOBAL_ATTRIBUTE19              ,
            cur.GLOBAL_ATTRIBUTE20              ,
            cur.COUNTRY_OF_ORIGIN_CODE          ,
            cur.TAX_USER_OVERRIDE_FLAG          ,
            cur.MATCH_OPTION                    ,
            cur.TAX_CODE_ID                     ,
            cur.CALCULATE_TAX_FLAG              ,
            cur.CHANGE_PROMISED_DATE_REASON     ,
            cur.NOTE_TO_RECEIVER                ,
            cur.SECONDARY_QUANTITY              ,
            cur.SECONDARY_UNIT_OF_MEASURE       ,
            cur.PREFERRED_GRADE                 ,
            cur.SECONDARY_QUANTITY_RECEIVED     ,
            cur.SECONDARY_QUANTITY_ACCEPTED     ,
            cur.SECONDARY_QUANTITY_REJECTED     ,
            cur.SECONDARY_QUANTITY_CANCELLED    ,
            cur.VMI_FLAG                        ,
            cur.CONSIGNED_FLAG                  ,
            cur.RETROACTIVE_DATE                ,
            cur.SUPPLIER_ORDER_LINE_NUMBER      
       ); 
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   	 
   	 update PO_LINE_LOCATIONS_ALL l
     set    from_line_location_id = CAPPOIMP_MAPPING.GetPOLineLocationIDMapping(l.from_line_location_id)
     where  from_line_location_id is not null
     and    exists (select 'X'
                    from   CRST_PO_LINE_LOCATIONS_ALL cl
                    where  cl.line_location_id_n = l.line_location_id);
     
     commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_PURCHASE_ORDER_LINE_LOCATION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
